package jsyf.oa.dao.system;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;

import org.springframework.stereotype.Repository;

import java.util.List;

import jsyf.oa.entity.system.WechatContactsEntity;
import jsyf.oa.idao.BaseDao;
import jsyf.oa.ientity.IBaseEntity;

/**
 * Created by jjx on 2018/3/19.
 */
@Repository
public class WechatContactsDao extends BaseDao {

    public WechatContactsDao() {
        super.iBaseEntity = new WechatContactsEntity();
    }

    @Override
    public IBaseEntity convertJson2Entity(JSONObject json) {
        return JSON.toJavaObject(json, WechatContactsEntity.class);
    }

    public List<JSONObject> getByUserId(String userId, int requestPage, int pageSize) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n");
        sql.append("t_wechat_friends_contacts.Id AS id,\n ");
        sql.append("t_wechat_friends_contacts.USER_ID AS userId,\n");
        sql.append("t_wechat_friends_contacts.USER_NAME AS userName,\n");
        sql.append("t_wechat_friends_contacts.SCREENSHOTS_URL AS screenshotsUrl,\n");
        sql.append("t_wechat_friends_contacts.CONTACTS AS contacts,\n");
        sql.append("t_wechat_friends_contacts.CREATE_TIME AS createTime\n");
        sql.append("from t_wechat_friends_contacts\n");
        sql.append("left join t_sys_user \n");
        sql.append("on t_sys_user.id=t_wechat_friends_contacts.user_id \n");
        sql.append("WHERE t_wechat_friends_contacts.DEL_FLAG =0\n");
        sql.append("AND t_sys_user.job_state !=1\n");
        sql.append("AND USER_ID='" + userId + "'\n");
        sql.append("ORDER BY T_WECHAT_FRIENDS_CONTACTS.CREATE_TIME DESC\n");
        super.appendPageSql(sql, requestPage, pageSize);
        List<JSONObject> jsonList = super.queryForJsonList(sql.toString());
        return jsonList == null ? null : jsonList;
    }

    public List<JSONObject> getByUserName(String userName, int requestPage, int pageSize) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n");
        sql.append("t_wechat_friends_contacts.id AS id,\n ");
        sql.append("t_wechat_friends_contacts.USER_ID AS userId,\n");
        sql.append("t_wechat_friends_contacts.USER_NAME AS userName,\n");
        sql.append("t_wechat_friends_contacts.SCREENSHOTS_URL AS screenshotsUrl,\n");
        sql.append("t_wechat_friends_contacts.CONTACTS AS contacts,\n");
        sql.append("t_wechat_friends_contacts.CREATE_TIME AS createTime\n");
        sql.append("from t_wechat_friends_contacts\n");
        sql.append("left join t_sys_user \n");
        sql.append("on t_sys_user.id=t_wechat_friends_contacts.user_id \n");
        sql.append("WHERE t_wechat_friends_contacts.DEL_FLAG =0\n");
        sql.append("AND t_sys_user.job_state=0\n");
        sql.append("AND t_sys_user.del_flag=0\n");
        sql.append("AND USER_Name='" + userName + "'\n");
        sql.append("ORDER BY T_WECHAT_FRIENDS_CONTACTS.CREATE_TIME DESC\n");
        super.appendPageSql(sql, requestPage, pageSize);
        List<JSONObject> jsonList = super.queryForJsonList(sql.toString());
        return jsonList == null ? null : jsonList;
    }

    /**
     * 获取公司所有大区及人员
     *
     * @return
     */
    public List<JSONObject> getRegionAndUserListByCompany(String companyId) {
        StringBuffer buffer = new StringBuffer();
        buffer.append("SELECT a.*,REPLACE(b.`name`, '大区', '') as bigRegionName,c.username as userName,  ");
        buffer.append("c.userid as userId,d.roleName,d.jobState from (   ");
        buffer.append("select id as regionId,REPLACE(`name`, '大区', '') as regionName,parent_id as parentId ");
        buffer.append("from t_sys_region where company_id='" + companyId + "') as a  ");
        buffer.append("LEFT JOIN t_sys_region as b on a.parentId=b.id LEFT JOIN v_region_manager as c  ");
        buffer.append("on a.regionId=c.regionid LEFT JOIN v_sys_user as d on c.userid=d.id  AND d.jobState!=1 order by parent_id asc  ");
        return queryForJsonList(buffer.toString());
    }


    /**
     * 获取某个公司好友数汇总
     *
     * @return
     */
    public JSONObject getContactsByCompanyId(String companyId) {
        JSONObject result = new JSONObject();
        StringBuffer sbPeople = new StringBuffer();
        Integer contacts = 0;
        //某个公司人
        sbPeople.append("SELECT id as userId from v_sys_user where companyId='" + companyId + "'  and jobState!=1 ");
        sbPeople.append("AND roleName in ('区域经理','大区经理') ");
        if (!sbPeople.toString().equals("")) {
            List<JSONObject> userList = queryForJsonList(sbPeople.toString());
            if (userList.size() > 0) {
              for (Integer i=0 ; i< userList.size(); i++){
                  String userId = userList.get(i).getString("userId");
                List<JSONObject>  jsonObjects = this.getByUserId(userId,1,1000);
                for (JSONObject jsonObject: jsonObjects){
                    Integer contact = jsonObject.getInteger("contacts");
                    contacts += contact;
                }
              }
            }
        }
        result.put("contacts",contacts);
        return  result;


    }

    /**
     * 获取集团下所有公司
     *
     * @return
     */
    public List<JSONObject> getCompanyList() {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT * from v_sys_company WHERE companyParentId!='0' and companyDelFlag='0' AND companyName not like '%大客户%' ");
        return queryForJsonList(sb.toString());
    }

}
